package urlconfig;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.time.LocalDateTime;

/**
 * @author 01395265
 * @date 2020/7/27
 */
public class TestBatchInsert {

    public static void main(String[] args) throws Exception {
        Connection connection = getConnection();
        long start = System.currentTimeMillis();
//        insertNormal(connection);
        insertBatch(connection);
        long end = System.currentTimeMillis();
        System.out.println(end - start);
        closeConnection(connection);
    }

    // 76507
    public static void insertNormal(Connection connection) throws Exception {
        String sql = "insert into date_url_test(date_time, time_stamp) values (?, ?)";
        PreparedStatement statement = connection.prepareStatement(sql);
        LocalDateTime parse = LocalDateTime.parse("2020-01-02T01:00:00");
        for (int i = 0; i < 50000; i++) {
            statement.setTimestamp(1, Timestamp.valueOf(parse));
            statement.setTimestamp(2, Timestamp.valueOf(parse));
            int j = statement.executeUpdate();
            if (j <= 0) {
                System.out.println("插入数据失败！");
            }
        }
    }

    // 73435
    public static void insertBatch(Connection connection) throws Exception {
//        connection.setAutoCommit(false);
        String sql = "insert into date_url_test(date_time, time_stamp) values (?, ?)";
        PreparedStatement statement = connection.prepareStatement(sql);
        LocalDateTime parse = LocalDateTime.parse("2020-01-02T01:00:00");
        for (int i = 0; i < 50000; i++) {
            statement.setTimestamp(1, Timestamp.valueOf(parse));
            statement.setTimestamp(2, Timestamp.valueOf(parse));
            statement.addBatch();
            if (i % 1000 == 0) {
                statement.executeBatch();
//                connection.commit();
                statement.clearBatch();
            }
        }
    }

    private static Connection getConnection() {
        String jdbcUrl = "jdbc:mysql://10.88.26.129:3307/auth?rewriteBatchedStatements=true";
        try {
            DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            return DriverManager.getConnection(jdbcUrl, "authopr", "authopr#d3");
        } catch (SQLException exception) {
            exception.printStackTrace();
        }
        return null;
    }

    private static void closeConnection(Connection connection) {
        try {
            connection.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

}
